package itsec1.customermanagement;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.security.InvalidKeyException;
import java.security.Signature;
import java.security.SignatureException;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;

public class CustomerDAO {
	// get customer data by customerid from db (incl. decrypt data)
	public static String getField(CustomerManagement cm, String field,
			String customerid) throws IOException {	
		//first verify whether data in db wasn't manipulated by checking signature
		//if data doesn't fit to signature -> request denied
		if(!verifyCustomerData(cm,customerid)){
			return null;
		}
		
		Connection conn = cm.getDBConnection();
		String result = null;

		String sql = "SELECT " + field + " FROM customer WHERE customerid=?";

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, customerid);

			ResultSet rs = pstmt.executeQuery();
			int count = 0;

			while (rs.next()) {
				count++;
				Blob resultBlob = rs.getBlob(field);

				if (resultBlob != null) {
					result = cm.decrypt(resultBlob.getBytes(1,
							(int) resultBlob.length()));
				}
			}

			if (count == 1) {
				return result;
			} else {
				// more than one (should not occur) or no customer with id found
				return null;
			}
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
			e.printStackTrace();

			return null;
		}
	}

	// updates field with new value -> returns true if successful (false if not)
	public static boolean setField(CustomerManagement cm, String field,
			String customerid, String valueText) throws IOException {
		//first verify whether data in db wasn't manipulated by checking signature
		//if data doesn't fit to signature -> request denied
		if(!verifyCustomerData(cm,customerid)){
			return false;
		}
		
		Connection conn = cm.getDBConnection();

		String sql = "UPDATE customer SET " + field + "=?, signature=? WHERE customerid=?";

		try {
			byte[] value;

			if (field.equals("password")) {
				value = cm.encrypt(HashUtil.encodePassword(valueText));
			} else {
				value = cm.encrypt(valueText);
			}
			byte[] newSignature= getUpdatedDataSignature(cm,customerid,field,value,null,null);

			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setBinaryStream(1, new ByteArrayInputStream(value),
					value.length);
			pstmt.setBinaryStream(2, new ByteArrayInputStream(newSignature),
					newSignature.length);
			pstmt.setString(3, customerid);

			pstmt.execute();
			pstmt.close();

			return true;
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
			e.printStackTrace();
		}

		// close connection
		try {
			if (conn != null && !conn.isClosed())
				conn.close();
		} catch (SQLException e) {
		}

		return false;
	}

	// encrypts strings before inserting customer
	public static Integer insertCustomer(CustomerManagement cm,
			String firstname, String lastname, String sex, String birthdate,
			String address, String zipcode, String phone, String email,
			String closed, String closedate, String username, String password)
			throws IOException {

		Connection conn = cm.getDBConnection();
		Signature signature = cm.getSignature();
		Integer id = null;

		// @formatter:off
		String sql= "INSERT INTO customer SET " +
				"firstname=?," +
				"lastname=?," +
				"sex=?," +
				"birthdate=?," +
				"address=?," +
				"zipcode=?," +
				"phone=?," +
				"email=?," +
				"closed=?," +
				"closedate=?," +
				"username=?," +
				"password=?," +
				"signature=?";
		// @formatter:on

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql,
					Statement.RETURN_GENERATED_KEYS);
			byte[] firstnameBytes= cm.encrypt(firstname);
			byte[] lastnameBytes= cm.encrypt(lastname);
			byte[] sexBytes= cm.encrypt(sex);
			byte[] birthdateBytes= cm.encrypt(birthdate);
			byte[] addressBytes= cm.encrypt(address);
			byte[] zipcodeBytes= cm.encrypt(zipcode);
			byte[] phoneBytes= cm.encrypt(phone);
			byte[] emailBytes= cm.encrypt(email);
			byte[] closedBytes= cm.encrypt(closed);
			byte[] closedateBytes= cm.encrypt(closedate);
			byte[] usernameBytes= cm.encrypt(username);
			byte[] passwordBytes= cm.encrypt(HashUtil.encodePassword(password));
						
			pstmt.setBytes(1, firstnameBytes);
			pstmt.setBytes(2, lastnameBytes);
			pstmt.setBytes(3, sexBytes);
			pstmt.setBytes(4, birthdateBytes);
			pstmt.setBytes(5, addressBytes);
			pstmt.setBytes(6, zipcodeBytes);
			pstmt.setBytes(7, phoneBytes);
			pstmt.setBytes(8, emailBytes);
			pstmt.setBytes(9, closedBytes);
			pstmt.setBytes(10, closedateBytes);
			pstmt.setBytes(11, usernameBytes);
			pstmt.setBytes(12, passwordBytes);

			signature.initSign(cm.getKeyPair().getPrivate());
			signature.update(firstnameBytes);
			signature.update(lastnameBytes);
			signature.update(sexBytes);
			signature.update(birthdateBytes);
			signature.update(addressBytes);
			signature.update(zipcodeBytes);
			signature.update(phoneBytes);
			signature.update(emailBytes);
			signature.update(closedBytes);
			signature.update(closedateBytes);
			signature.update(usernameBytes);
			signature.update(passwordBytes);

			pstmt.setBytes(13, signature.sign());

			pstmt.execute();

			ResultSet rs = pstmt.getGeneratedKeys();

			if (rs.next()) {
				id = rs.getInt(1);
			}

			pstmt.close();

		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
			e.printStackTrace();
		} catch (InvalidKeyException e) {
			e.printStackTrace();
		} catch (SignatureException e) {
			e.printStackTrace();
		}

		// close connection
		try {
			if (conn != null && !conn.isClosed())
				conn.close();
		} catch (SQLException e) {
		}

		return id;
	}
	
	public static boolean deleteCustomer(CustomerManagement cm,
			String customerid) throws IOException {
		//first verify whether data in db wasn't manipulated by checking signature
		//if data doesn't fit to signature -> request denied
		if(!verifyCustomerData(cm,customerid)){
			return false;
		}
		
		Connection conn = cm.getDBConnection();

		String sql = "UPDATE customer SET closed=?, closedate=?, signature=? WHERE customerid=?";

		try {
			byte[] closed = cm.encrypt("Y");
			// get current date
			Date date = new Date();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			byte[] closedate = cm.encrypt(sdf.format(date));
			
			byte[] newSignature= getUpdatedDataSignature(cm,customerid,"closed",closed,"closedate",closedate);

			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setBinaryStream(1, new ByteArrayInputStream(closed),
					closed.length);
			pstmt.setBinaryStream(2, new ByteArrayInputStream(closedate),
					closedate.length);
			pstmt.setBinaryStream(3, new ByteArrayInputStream(newSignature),
					newSignature.length);
			pstmt.setString(4, customerid);

			pstmt.execute();
			pstmt.close();

			return true;
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
			e.printStackTrace();
		}

		// close connection
		try {
			if (conn != null && !conn.isClosed())
				conn.close();
		} catch (SQLException e) {
		}

		return false;
	}

	public static boolean autenticateCustomer(CustomerManagement cm,
			String username, String password, String customerid)
			throws IOException {		
		//first verify whether data in db wasn't manipulated by checking signature
		//if data doesn't fit to signature -> request denied
		if(!verifyCustomerData(cm,customerid)){
			return false;
		}
		
		Connection conn = cm.getDBConnection();
		String dbUsername = null;
		byte[] dbPasswordHash = null;
		byte[] passwordHash = HashUtil.encodePassword(password);
		boolean authenticated = false;

		String sql = "SELECT username,password FROM customer WHERE customerid=?";

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, customerid);

			ResultSet rs = pstmt.executeQuery();
			int count = 0;

			while (rs.next()) {
				count++;
				Blob usernameBlob = rs.getBlob("username");
				Blob passwordBlob = rs.getBlob("password");

				if (usernameBlob != null && passwordBlob != null) {
					dbUsername = cm.decrypt(usernameBlob.getBytes(1,
							(int) usernameBlob.length()));
					dbPasswordHash = cm.decryptAsBytes(passwordBlob.getBytes(1,
							(int) passwordBlob.length()));

					if (dbUsername.equals(username)
							&& Arrays.equals(dbPasswordHash, passwordHash))
						authenticated = true;
				}
			}

			if (count == 1 && authenticated) {
				return true;
			} else {
				// more than one (should not occur) or no customer with id found
				return false;
			}
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
			e.printStackTrace();

			return false;
		}
	}
	
	private static boolean verifyCustomerData(CustomerManagement cm,String customerid){
		Connection conn = cm.getDBConnection();
		Signature signature=null;
		signature = cm.getSignature();
		try {
			signature.initVerify(cm.getKeyPair().getPublic());
		} catch (InvalidKeyException e1) {
			e1.printStackTrace();
		}
		
		boolean verified= false;
		
		// @formatter:off
		String sql= "SELECT firstname," +
				"lastname," +
				"sex," +
				"birthdate," +
				"address," +
				"zipcode," +
				"phone," +
				"email," +
				"closed," +
				"closedate," +
				"username," +
				"password," +
				"signature FROM customer WHERE customerid=?";
		// @formatter:on
		
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, customerid);

			ResultSet rs = pstmt.executeQuery();

			if (rs.next()) {				
				for(int i=1; i <= 12; i++){
					Blob resultBlob = rs.getBlob(i);
					signature.update(resultBlob.getBytes(1,
							(int) resultBlob.length()));				
				}
				Blob signatureBlob= rs.getBlob("signature");
				
				verified= signature.verify(signatureBlob.getBytes(1,
						(int) signatureBlob.length()));
			}
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
			e.printStackTrace();
		} catch (SignatureException e) {
			e.printStackTrace();
		}
		
		// close connection
		try {
			if (conn != null && !conn.isClosed())
				conn.close();
		} catch (SQLException e) {		}
		
		return verified;
	}
	
	private static byte[] getUpdatedDataSignature(CustomerManagement cm,String customerid,String field1,byte[] value1
			,String field2,byte[] value2) throws IOException{
		Connection conn = cm.getDBConnection();
		Signature signature=null;
		byte[] sig= null;
		
		signature = cm.getSignature();
		try {
			signature.initSign(cm.getKeyPair().getPrivate());
		} catch (InvalidKeyException e1) {
			e1.printStackTrace();
		}
		
		// @formatter:off
		String sql= "SELECT firstname," +
				"lastname," +
				"sex," +
				"birthdate," +
				"address," +
				"zipcode," +
				"phone," +
				"email," +
				"closed," +
				"closedate," +
				"username," +
				"password" +
				" FROM customer WHERE customerid=?";
		// @formatter:on
		
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, customerid);

			ResultSet rs = pstmt.executeQuery();

			if (rs.next()) {
				int updateField1= rs.findColumn(field1);
				int updateField2= -1;
				if(field2 != null && value2 != null)
					updateField2= rs.findColumn(field2);
					
				
				for(int i=1; i <= 12; i++){
					if(i != updateField1 && i != updateField2 ){
						Blob resultBlob = rs.getBlob(i);
						signature.update(resultBlob.getBytes(1,
								(int) resultBlob.length()));
					}else{
						if(i == updateField1)
							signature.update(value1);
						else
							signature.update(value2);
					}
				}
				sig= signature.sign();
			}
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
			e.printStackTrace();
		} catch (SignatureException e) {
			e.printStackTrace();
		}
		
		// close connection
		try {
			if (conn != null && !conn.isClosed())
				conn.close();
		} catch (SQLException e) {		}
		
		return sig;
	}
}
